import threading
import time
import datetime

from Extend.GrandClass import GrandClass
from Extend.Log import LogMethod
from Extend.SqlHelper import SqlServer
from Extend.NoSql import NoSqlDataGet
import gevent
from Extend.LocalError import LocalSqlServerError
from Extend.NoSql import Heart


class TargetDatabase:
    def __init__(self, server, user, password, db):
        self.server = server
        self.db = SqlServer(self.server, user, password, db)
        # 需要查询的表
        self.tables = ['IDBSPP', 'IMOS', 'IDBATT', 'IDBVERSO', 'FAVORITES', 'IDBGPL', 'CMSINCIDENTPRICE',
                       'IDBGRPS', 'IDBPRF', 'IDBGEOM', 'CMSINCIDENTTEXT', 'IMORDFOLDER', 'IDBVAR', 'IDBWG',
                       'DATACHANGELOG', 'PROADMIN', 'DRAWREF', 'CMSINCIDENT', 'IDBREFS', 'IDBPURCH', 'IDBINFO']
        # 需要新增ID序号的表
        self.need_remove_id = {'PROADMIN': "ID", 'IMORDFOLDER': "DIR_ID"}
        # 不需要删除源数据的表
        self.not_delete = ('PROADMIN')

    def updateOrdedrInfo(self, order_no, l_change):
        sql = """update [PROADMIN] set LCHANGE='{1}' where [NAME] = '{0}' """.format(order_no, l_change)
        return self.db.changeData(sql)

    def getAll(self, table, order_no):
        condition = self.getCondition(table)
        sql = """select * from [{}] where [{}]='{}'""".format(table, condition, order_no)
        result = self.db.getAllData(sql)
        # print(sql)
        return table, result

    def removeData(self, table, order_no):
        condition = self.getCondition(table)
        sql = """delete from [{}] where [{}]='{}'""".format(table, condition, order_no)
        return self.db.changeData(sql)

    def getCondition(self, table_name):
        if table_name in ("PROADMIN", "IMORDFOLDER", "DATACHANGELOG"):
            return 'NAME'
        elif table_name == "FAVORITES":
            return 'INORDER'
        elif table_name == "CAMNCNOINFO":
            return 'SUBPARTID'
        elif table_name in ["CMSINCIDENTTEXT", "CMSINCIDENTPRICE", "CMSINCIDENT"]:
            return 'ORDERNAME'
        else:
            return 'ORDERID'

    def getMaxId(self, table):
        sql = "select max(id) id from [{}]".format(table)
        try:
            max_id = self.db.getOneData(sql)['id']
        except:
            max_id = -1
        return max_id


class DataControl(GrandClass):
    def __init__(self, local_server: dict, target_server_list: list):
        super().__init__()
        self.local_server = self._addDataServer(local_server)
        self.recovery_server = []
        self.target_servers = []
        self._addServer(target_server_list)

    @staticmethod
    def _addDataServer(server_dict):
        server = server_dict['Server']
        user = server_dict['User']
        password = server_dict['Password']
        db = server_dict['DataBase']
        database = TargetDatabase(server, user, password, db)
        return database

    def _addServer(self, server_list: list):
        for x in server_list:
            self.target_servers.append(self._addDataServer(x))

    def _recoveryServer(self, server):
        for x in self.target_servers + self.recovery_server:
            if x is server:
                return
        self.recovery_server.append(server)

    def recoveryServerToRun(self):
        while self.recovery_server:
            server = self.recovery_server.pop()
            self.target_servers.append(server)
            LogMethod.addLog([7, "服务器:%s 已经恢复数据库读取" % server.server, ""])

    def addServer(self, server_list: list):
        self.clearServeList()
        self._addServer(server_list)

    # 循环主逻辑
    def startMonitors(self):
        while Heart.getMeHeart(self.__class__.__name__, ):
            super().startMonitors()
            if len(self.recovery_server) > 0:
                self.recoveryServerToRun()
                continue
            for x in NoSqlDataGet.GetNeed(1, 'S'):
                for y in self.target_servers:
                    if x["Server"] == y.server:
                        this_server = y
                        break
                else:
                    continue
                try:
                    result = self.dataFactory(this_server, x)
                    if result is not None:
                        LogMethod.addLog([69, "未查询到订单%s信息" % x['OrderNO'], result + "info:%s" % str(x)])
                    NoSqlDataGet.AppendSuccess('S', x)
                except Exception as err:
                    try:
                        self.removeServer(this_server, str(err) + '\t错误发生订单号:%s' % x)
                    except LocalSqlServerError as error:
                        LogMethod.addLog([8, "本地数据库异常", str(error)])
                        Heart.setMeStat(self.__class__.__name__, False)
                        Heart.setMeHeart(self.__class__.__name__, False)
                        return
                    except Exception as error:
                        LogMethod.addLog([9, "未知异常，具体错误请查询详情", str(error)])
            gevent.sleep(1)  # 工作太快协程会不检测服务器状态
        Heart.setMeStat(self.__class__.__name__, False)
        # print('结束关闭')

    def removeServer(self, server, error):
        if server is self.local_server:
            raise LocalSqlServerError(error)
        for i in range(len(self.target_servers)):
            if self.target_servers[i] is server:
                self.target_servers.pop(i)
                break
        LogMethod.addLog([6, "服务器:%s 无法连接数据库或无法读取对应表内容" % server.server, str(error)])
        gevent.spawn(MonitorServer.heartbeatDetection, server, "PROADMIN", self._recoveryServer)

    def clearServeList(self):
        for i in range(len(self.target_servers)):
            server = self.target_servers.pop(i)
            del server

    def dataFactory(self, this_server: TargetDatabase, message: dict):
        order_no = message['OrderNO']
        # 获取源数据
        dict_content = DatabaseAdvancedOperations.getNeedData(this_server, order_no)
        if 'PROADMIN' not in dict_content.keys():
            return '服务器：%s ,订单%s,IMOS日志为成功，但是未找到数据' % (this_server.server, order_no)
        # 删除本地数据
        DatabaseAdvancedOperations.removeData(self.local_server, order_no, dict_content)
        # 更新添加本地数据
        DatabaseAdvancedOperations.insertNewData(self.local_server, dict_content)
        # 更新一下数据，测试一波
        DatabaseAdvancedOperations.updateTableId(self.local_server, order_no)
        return None


class DatabaseAdvancedOperations:
    # 获取各个表的内容
    @staticmethod
    def getNeedData(server: TargetDatabase, order_no):
        result = {}
        for i in server.tables:
            x, y = server.getAll(i, order_no)
            if y:
                result[x] = y
        return result

    # 删除数据
    @staticmethod
    def removeData(server: TargetDatabase, order_no, data: dict):
        for table, key in server.need_remove_id.items():
            DatabaseAdvancedOperations.removeID(data[table], key)
        for x, y in data.items():
            if x in server.not_delete:
                continue
            server.removeData(x, order_no)

    @staticmethod
    def updateTableId(server: TargetDatabase, order_no):
        cms_filed = server.getCondition("CMSINCIDENT")
        admin_filed = server.getCondition("PROADMIN")
        folder_filed = server.getCondition("IMORDFOLDER")
        # 获取PROADMIN的ID
        sql = "select ID from PROADMIN where [{}] = '{}'".format(admin_filed, order_no)
        admin_id = server.db.getOneData(sql)['ID']
        sql = "select DIR_ID from [IMORDFOLDER] where [{}] = '{}'".format(folder_filed, order_no)
        # 获取IMORDFOLDER的ID
        folder_id = server.db.getOneData(sql)['DIR_ID']
        sql = "update [CMSINCIDENT] set [{1}] = {2} where [ORDERNAME]='{0}'".format(order_no, cms_filed, admin_id)
        server.db.updateOrInsert(sql)
        sql = "update [PROADMIN] set [PRODUCTIONID]={} where ID = {}".format(folder_id, admin_id)
        server.db.updateOrInsert(sql)

    # 删除值的当中的需要移除的字段
    @staticmethod
    def removeID(data, key):
        if type(data) is list:
            for dt in data:
                del dt[key]
        else:
            del data[key]

    # 插入数据
    @staticmethod
    def insertNewData(server: TargetDatabase, data: dict):
        order_admin = data.pop("PROADMIN")
        order_admin = order_admin if type(order_admin) is dict else order_admin[0]
        influence_row = server.updateOrdedrInfo(order_admin['NAME'], order_admin['LCHANGE'])
        if influence_row == 0:
            DatabaseAdvancedOperations.insertRow(server, 'PROADMIN', order_admin)
        # thread_pool = ThreadPoolExecutor(16)
        threads = []
        for table, table_values in data.items():
            # t = thread_pool.submit(DatabaseAdvancedOperations.insertTableValues, (server, table, table_values))
            # threads.append(t)
            t = threading.Thread(target=DatabaseAdvancedOperations.insertTableValues,
                                 args=(server, table, table_values,))
            t.start()
            threads.append(t)
        for x in threads:
            x.join()
        # wait(threads, return_when=ALL_COMPLETED)

    # 向某个表插入数据
    @staticmethod
    def insertTableValues(server: TargetDatabase, table, table_values):
        sql_list = []
        for val in table_values:
            sql = DatabaseAdvancedOperations.getSql(table, val)
            sql_list.append(sql)
        server.db.commitAll(sql_list)

    @staticmethod
    def insertRow(server, table, val):
        sql = DatabaseAdvancedOperations.getSql(table, val)
        server.db.addData(sql)

    @staticmethod
    def getSql(table, val):
        column, values = '', ''
        for x, y in val.items():
            column += '[' + str(x) + '],'
            if y is None:
                values += 'null,'
            elif type(y) is int:
                values += str(y) + ","
            elif type(y) is datetime.datetime:
                values += "'" + str(y)[0:str(y).rfind('.')] + "',"
            else:
                values += "N'" + str(y) + "',"
        column = column.rstrip(',')
        values = values.rstrip(',')
        sql = 'insert into [{}]({}) values({})'.format(table, column, values)
        return sql


class MonitorServer:

    @staticmethod
    def heartbeatDetection(obj: TargetDatabase, table: str, return_func, loop=0):
        while True:
            try:
                obj.getMaxId(table)
                break
            except Exception as e:
                loop += 1
                if loop <= 50:
                    time.sleep(5)
                elif loop <= 100:
                    time.sleep(10)
                else:
                    time.sleep(20)
        return_func(obj)


if __name__ == '__main__':
    dc = DataControl({'Server': '172.16.200.251', 'User': 'sa', 'Password': '2020', 'DataBase': 'LWJ'},
                     [{'Server': '172.16.200.253', 'User': 'sa', 'Password': '2020', 'DataBase': 'LWJ'},
                      {'Server': '172.16.200.252', 'User': 'sa', 'Password': '2020', 'DataBase': 'LWJ'}])
    dc.start()
